package dnbW.dbW.sucxentdbW;

import dnbW.dbW.Document;
import dnbW.entityW.Cardinality;
import dnbW.utilW.SubsetSumSolver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

/**
 * This object is in charge of managing/finding the mapping between ID and IDREF
 * attributes in a data source
 * @author truongbaquan
 */
public class KeyAttributesManager {
    private SucxentDataSource dataSource;
    private Map<Integer, IdPath> idPathMap;

    public KeyAttributesManager(SucxentDataSource dataSource) {
        this.dataSource = dataSource;
        this.findIdRelationships();
    }

    /**
     * Finds a list of ID/IDREF relationships group by ID attributes
     * @return
     */
    public Collection<IdPath> findIdRelationships() {
        try {
            if (SucxentDatabaseEngine.isTableExisting(this.dataSource.getDatabase(), this.dataSource.getName() + "_IdReferences")) {
                this.loadFromDatabase();
            }
            else {
                if (!this.dataSource.getLeafPathManager().isPathsInitialized())
                    this.dataSource.getLeafPathManager().loadLeafPaths();
                boolean[] isKeyPath = this.loadKeyPaths();
                Set<ILeafPath> idPathSet = this.findIdAttributes(this.dataSource.getDocuments(), isKeyPath);
                findIdRefAttributes(idPathSet, isKeyPath);
                this.createDatabaseTable();
                this.insertIntoDatabase(this.idPathMap.values());
            }
        }
        catch (SQLException ex) {
            System.err.println("Exceptions in finding ID/IDREF");
            System.err.println(ex.getMessage());
            ex.printStackTrace(System.err);
        }
        return this.idPathMap.values();
    }

    /**
     * Finds potential ID attributes in a list of document. An ID attribute in a list of document
     * must be an ID attribute in all documents
     * @param documents List of documents
     * @param isKeyPath     The flag indicating whether the path at each pathId is a key path
     * @return          List of ID attributes
     */
    private Set<ILeafPath> findIdAttributes(Collection<Document> documents, boolean[] isKeyPath) {
        Set<ILeafPath> idPathSet = new TreeSet<ILeafPath>();
        boolean first = true;
        for (Document document : documents) {
            Collection<ILeafPath> idPaths = this.findIdAttributes(document, isKeyPath);
            if (first) {
                idPathSet.addAll(idPaths);
                first = false;
            }
            else {
                idPathSet.retainAll(idPaths);
            }
        }
        return idPathSet;
    }

    /**
     * Finds potential ID attributes of an document. An attribute must satisfy two conditions
     * 1. It must have no null values and whitespaces (i.e. it must be a key attribute)
     * 2. It must be unique. That is, there are no identical values of this attribute in the document
     * @param document      The considered document
     * @param isKeyPath     The flag indicating whether the path at each pathId is a key path
     * @return              
     */
    private Collection<ILeafPath> findIdAttributes(Document document, boolean[] isKeyPath) {
        try {
            ResultSet results;
            String sql;
            Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");

            // find the number of leaves for each path in the given document
            long[] numLeaves = new long[isKeyPath.length];
            sql = "select PathId, COUNT(*) as num from " + this.dataSource.getName() + "_Attribute where DocId = " + document.getDocId() + " group by PathId";
            results = statement.executeQuery(sql);
            while (results.next()) {
                int pathId = results.getInt("PathId");
                if (isKeyPath[pathId])
                    numLeaves[pathId] = results.getLong("num");
            }
            sql = "select PathId, COUNT(*) as num from " + this.dataSource.getName() + "_PathValue where DocId = " + document.getDocId() + " group by PathId";
            results = statement.executeQuery(sql);
            while (results.next()) {
                int pathId = results.getInt("PathId");
                if (isKeyPath[pathId])
                    numLeaves[pathId] = results.getLong("num");
            }

            // find the number of distinct values of the attribute in the document; if all values are unique,
            // this number must be equal to the number of values of the attribute in the document
            String attribTemplate = "select count(distinct LeafValue) from " + this.dataSource.getName() + "_Attribute where DocId = " + document.getDocId() + " and PathId=#PathId#";
            String elementTemplate = "select count(distinct LeafValue) from " + this.dataSource.getName() + "_PathValue where DocId = " + document.getDocId() + " and PathId=#PathId#";
            List<ILeafPath> idPaths = new LinkedList<ILeafPath>();
            for (int pathId = 1; pathId < isKeyPath.length; pathId++) {
                if (isKeyPath[pathId])
                {
                    if (numLeaves[pathId] > 0)
                    {
                        LeafPath leafPath = this.dataSource.getLeafPathManager().getPath(pathId);
                        if (leafPath.isAttribute())
                            sql = attribTemplate.replace("#PathId#", "" + pathId);
                        else
                            sql = elementTemplate.replace("#PathId#", "" + pathId);
                        results = statement.executeQuery(sql);
                        results.next();
                        long totalRow = results.getInt(1);
                        if (totalRow == numLeaves[pathId]) { // the number of unique values = the number of values
                            IdPath idPath = new IdPath(leafPath);
                            idPaths.add(idPath);
                        }
                    }
                }
            }
            results.close();
            statement.close();
            return idPaths;
        }
        catch (SQLException ex) {
            System.err.println("Error in finding key attributes");
            ex.printStackTrace(System.err);
            return null;
        }
    }

    /**
     * Returns a list of ID attributes with all IDREF attributes matching to them.
     * Among the ID attributes in the input potential list, only the ID attributes with
     * at least one IDREF attribute are in the final list
     * @param potentialIdPathSet    Potential ID attribute list
     * @param isKeyPath             The flag indicating whether the path at each pathId is a key path
     * @return                      Final ID attribute list with IDREF mappings
     */
    private Map<Integer, IdPath> findIdRefAttributes(Set<ILeafPath> potentialIdPathSet, boolean[] isKeyPath) {
        this.idPathMap = new TreeMap<Integer, IdPath>();
        try {
            Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
            statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");
            String sql;

            // Gets the number of leaves in the data source for each path
            sql = "with FullTable(DocId, PathId, LeafValue) as (\n" +
                    "select DocId, PathId, LeafValue from #DataSrc#_Attribute\n" +
                    "union all\n" +
                    "select DocId, PathId, LeafValue from #DataSrc#_PathValue)\n" +
                  "select PathId, COUNT(*) as NumLeaves from FullTable IdRef\n" +
                    "where exists (select * from #DataSrc#_KeyPath IdRefPath where IdRef.PathId = IdRefPath.PathId)\n" +
                    "group by PathId\n";
            sql = sql.replaceAll("#DataSrc#", this.dataSource.getName());
            ResultSet sqlResults = statement.executeQuery(sql);
            int numLeaves[] = new int[isKeyPath.length]; // number of leaves for each path Id
            while (sqlResults.next()) {
                int pathId = sqlResults.getInt("PathId");
                numLeaves[pathId] = sqlResults.getInt("NumLeaves");
            }

            String pathIdList = SucxentDatabaseEngine.buildPathIdListString(potentialIdPathSet);
            System.out.println("Key IDs = " + pathIdList);

            // For each potential IDREF path, find the number of leaves which matches a value of each id path
            sql = "with FullTable(DocId, PathId, AncestorDeweyGroup, LeafValue) as (\n" +
                    "select DocId, PathId, AncestorDeweyGroup, LeafValue from #DataSrc#_Attribute\n" +
                    "union all\n" +
                    "select DocId, PathId, AncestorDeweyGroup, LeafValue from #DataSrc#_PathValue),\n" +
                    "KeyValues(DocId, PathId, LeafValue) as (\n" +
                    "select distinct DocId, PathId, LeafValue from FullTable where PathId in #PathIds#)\n" +

                    "select IdRef.PathId as IdRefPathId, Id.PathId as IdPathId, COUNT(*) as Num\n" +
                    "from FullTable IdRef join KeyValues Id on IdRef.DocId = Id.DocId and IdRef.LeafValue = Id.LeafValue and IdRef.PathId != Id.PathId\n" +
                    "where exists (select * from #DataSrc#_KeyPath IdRefPath where IdRef.PathId = IdRefPath.PathId)\n" +
                    "group by IdRef.PathId, Id.PathId order by IdRefPathId, Num, IdPathId\n";
            sql = sql.replaceAll("#DataSrc#", this.dataSource.getName());
            sql = sql.replaceAll("#PathIds#", pathIdList);
            sqlResults = statement.executeQuery(sql);

            List<Integer> idPathIds = new ArrayList<Integer>();
            List<Long> nums = new ArrayList<Long>();
            int curIdRefPathId, nextIdRefPathId;
            
            // advance the cursor and get first IDREF PathId
            nextIdRefPathId = sqlResults.next() ? sqlResults.getInt("IdRefPathId") : -1;
            // Is the next IDREF PathId empty (i.e. equal to -1)?
            while (nextIdRefPathId != -1) {
                // Not empty. Gets the data on the current cursor.
                curIdRefPathId = nextIdRefPathId;
                nums.add(sqlResults.getLong("Num"));
                idPathIds.add(sqlResults.getInt("IdPathId"));
                // Advances the cursor again and get the next IDREF PathId
                nextIdRefPathId = sqlResults.next() ? sqlResults.getInt("IdRefPathId") : -1;
                // If the next IDREF PathId is different from the current one, this IDREF is finished and tries to process it
                if (nextIdRefPathId != curIdRefPathId) {
                    // Stores the num leaves corresponding to each ID PathId to an array. By the SQL, it is sorted.
                    long[] numArray = new long[nums.size()];
                    for (int i = 0; i < numArray.length; i++) {
                        numArray[i] = nums.get(i);
                    }
                    // Uses a subset sum solver to find the bitstrings showing the list of ID PathId
                    // that could possibly form a partition on the values of IDREF
                    SubsetSumSolver solver = new SubsetSumSolver();
                    List<Long> bitstrings = solver.solve(numArray, numLeaves[curIdRefPathId]);
                    Collections.sort(bitstrings, new BitstringLengthComparator());

                    for (long bitstring : bitstrings) {
                        System.out.println(curIdRefPathId + " : " + this.dataSource.getLeafPathManager().getPath(curIdRefPathId).toPathExp());
                        // Traverse the bitstring to get the 1 bits and find the corresponding path ID
                        for (int i = 0; i < idPathIds.size(); i++) {
                            int idPathId = idPathIds.get(i);
                            if (bitstring % 2 == 1) {
                                IdPath idPath;
                                if (idPathMap.containsKey(idPathId))
                                    idPath = idPathMap.get(idPathId);
                                else {
                                    idPath = new IdPath(this.dataSource.getLeafPathManager().getPath(idPathId));
                                    idPathMap.put(idPathId, idPath);
                                }
                                IdRefPath idRefPath = new IdRefPath(this.dataSource.getLeafPathManager().getPath(curIdRefPathId));
                                IdRelation relation = idPath.addIdRef(idRefPath);
                                this.setCardinality(relation);
                                System.out.println("\t\t" + idPathId + " : " +
                                        this.dataSource.getLeafPathManager().getPath(idPathId).toPathExp() +
                                        " " + relation.getDbCardinality());
                            }
                            bitstring /= 2;
                        }
                        break; // only consider the longest bitstring (should move to the next one if the first one fails somehow)
                    }
                    nums.clear();
                    idPathIds.clear();
                }
            }
        }
        catch (SQLException ex) {
            System.err.println("Error in finding IDREF attributes");
            ex.printStackTrace(System.err);
        }
        return idPathMap;
    }

    /**
     * Creates IdReferences table in the data source
     * @throws SQLException
     */
    private void createDatabaseTable() throws SQLException {
        String sql = "create table " + this.dataSource.getName() + "_IdReferences (" +
                "ID int NOT NULL, " +
                "IDREF int NULL," +
                "Type char(10) NULL)";
        Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
        statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");
        statement.executeUpdate(sql);
        statement.close();
    }

    /**
     * Inserts into ID/IDREF information into IdReferences table
     * @param idPaths       ID/IDREF information
     * @throws SQLException
     */
    public void insertIntoDatabase(Collection<IdPath> idPaths) throws SQLException {
        Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
        statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");
        statement.executeUpdate("delete from " + this.dataSource.getName() + "_IdReferences");
        String sql;
        for (IdPath idPath : idPaths) {
            for (IdRelation idRelation : idPath.getRelations()) {
                String type = idRelation.getDbCardinality();
                sql = "INSERT INTO " + this.dataSource.getName() + "_IdReferences(ID, IDREF, Type)" +
                "VALUES (" + idPath.getPathId() + ", " + idRelation.getIdRef().getPathId() + ", '" + type + "')";
                statement.executeUpdate(sql);
            }
        }
        statement.close();
    }

    /**
     * Loads ID/IDREF information from the database
     * @throws SQLException
     */
    private void loadFromDatabase() throws SQLException {
        this.idPathMap = new TreeMap<Integer, IdPath>();
        String tableName = this.dataSource.getName() + "_IdReferences";
        if (SucxentDatabaseEngine.isTableExisting(this.dataSource.getDatabase(), tableName)) {
            String sql = "select ID, IDREF, Type from " + tableName + " order by ID, IDREF";
            Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
            ResultSet sqlResult = statement.executeQuery(sql);
            while (sqlResult.next()) {
                int id = sqlResult.getInt("ID");
                int idRef = sqlResult.getInt("IDREF");
                String type = sqlResult.getString("Type");
                IdPath idPath;
                if (this.idPathMap.containsKey(id))
                    idPath = idPathMap.get(id);
                else {
                    idPath = new IdPath(this.dataSource.getLeafPathManager().getPath(id));
                    this.idPathMap.put(id, idPath);
                }
                IdRefPath idRefPath = new IdRefPath(this.dataSource.getLeafPathManager().getPath(idRef));
                IdRelation relation = idPath.addIdRef(idRefPath);
                relation.setDbCardinality(type);
            }
        }
    }

    // <editor-fold defaultstate="collapsed" desc="Key Paths">
    /**
     * Creates KeyPath table in the data source.
     * This table is used to store a set of attributes (leaf path) satisfying some "key" conditions:
     * 1. It has no null values
     * 2. It has no whitespace
     * 3. It does not consist of only digits
     * All ID and IDREF attributes must be in this set of attributes
     * @throws SQLException
     */
    private void createKeyPathTable() throws SQLException {
        String sql = "with FullTable(DocId, PathId, AncestorDeweyGroup, LeafValue) as (\n" +
                "select DocId, PathId, AncestorDeweyGroup, LeafValue from #DataSrc#_Attribute\n" +
                "union all\n" +
                "select DocId, PathId, AncestorDeweyGroup, LeafValue from #DataSrc#_PathValue)\n" +
                "select KeyPaths.PathId, KeyPaths.PathExp, COUNT(*) as NumLeaves, COUNT(distinct LeafValue) as NumValues\n" +
                "into #DataSrc#_KeyPath\n" +
                "from FullTable A inner join \n" +
                "(select * from #DataSrc#_Path P where not exists (select * from FullTable where PathId = P.PathId and (LeafValue like '% %' or patindex('%[A-Za-z]%', LeafValue)=0 or LeafValue is null))) as KeyPaths\n" +
                "on A.PathId = KeyPaths.PathId\n" +
                "group by KeyPaths.PathId, KeyPaths.PathExp";
        sql = sql.replace("#DataSrc#", this.dataSource.getName());
        Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
        statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");
        statement.executeUpdate(sql);
    }

    /**
     * Loads the leaf paths (attributes) stored in the KeyPath table.
     * If the set of leaf paths are not in memory, load them. If the KeyPath table is not created, create it.
     * @throws SQLException
     */
    private boolean[] loadKeyPaths() throws SQLException {
        boolean[] isKeyPath = null;
        if (!SucxentDatabaseEngine.isTableExisting(this.dataSource.getDatabase(), this.dataSource.getName() + "_KeyPath"))
            this.createKeyPathTable();

        ResultSet results;
        Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
        statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");

        isKeyPath = new boolean[this.dataSource.getLeafPathManager().getNumberOfPaths()+1];
        int pathId;
        results = statement.executeQuery("select PathId from " + this.dataSource.getName() + "_KeyPath");
        while (results.next()) {
            pathId = results.getInt("PathId");
            isKeyPath[pathId] = true;
        }
        results.close();
        statement.close();
        return isKeyPath;
    }
    // </editor-fold>

    public void setCardinality(IdRelation relation) throws SQLException {
        Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
        statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");

        String attribTable = this.dataSource.getName() + "_Attribute";
        String elemTable = this.dataSource.getName() + "_PathValue";
        String idTable = (relation.getId().isAttribute()?attribTable:elemTable) + " ID";
        String idRefTable = (relation.getIdRef().isAttribute()?attribTable:elemTable) + " IDREF";

        String sql; ResultSet sqlResult;
        sql = "select ID.DocId, ID.AncestorDeweyGroup, COUNT(*) from " + idTable + " join " + idRefTable + "\n" +
                "on ID.DocId = IDREF.DocId and ' ' + IDREF.LeafValue + ' ' like '% ' + ID.LeafValue + '[ ,.?!]%'\n" +
                "where ID.PathId = #IDPathId# and IDREF.PathId = #IDREFPathId# group by ID.DocId, ID.AncestorDeweyGroup having COUNT(*) > 1";
        sql = sql.replace("#IDPathId#", ""+relation.getId().getPathId()).replace("#IDREFPathId#", ""+relation.getIdRef().getPathId());
        sqlResult = statement.executeQuery(sql);
        if (sqlResult.next()) relation.setIdRefCardinality(Cardinality.many);
        else relation.setIdRefCardinality(Cardinality.one);

        sql = "select IDREF.DocId, IDREF.AncestorDeweyGroup, COUNT(*) from " + idTable + " join " + idRefTable + "\n" +
                "on ID.DocId = IDREF.DocId and ' ' + IDREF.LeafValue + ' ' like '% ' + ID.LeafValue + '[ ,.?!]%'\n" +
                "where ID.PathId = #IDPathId# and IDREF.PathId = #IDREFPathId# group by IDREF.DocId, IDREF.AncestorDeweyGroup having COUNT(*) > 1";
        sql = sql.replace("#IDPathId#", ""+relation.getId().getPathId()).replace("#IDREFPathId#", ""+relation.getIdRef().getPathId());
        sqlResult = statement.executeQuery(sql);
        if (sqlResult.next()) relation.setIdCardinality(Cardinality.many);
        else relation.setIdCardinality(Cardinality.one);
    }

    // <editor-fold defaultstate="collapsed" desc="class BitstringLengthComparator">
    /**
     * A comparator class (using Comparator interface) to compare the number of '1' bits
     * in two bitstrings (the bitstrings are stored as long)
     */
    private class BitstringLengthComparator implements Comparator<Long> {

        @Override
        public int compare(Long o1, Long o2) {
            int bitCount1 = Long.bitCount(o1);
            int bitCount2 = Long.bitCount(o2);
            if (bitCount1 != bitCount2) {
                return Long.bitCount(o1) - Long.bitCount(o2);
            }
            else {
                return (int)(o1 - o2);
            }
        }
    }
    // </editor-fold>
}
